package com.forest.datachangetracker.db;

import cn.hutool.core.collection.CollUtil;
import cn.hutool.core.util.StrUtil;
import com.forest.datachangetracker.enums.DbTypeEnum;
import lombok.SneakyThrows;
import lombok.extern.slf4j.Slf4j;
import net.sf.jsqlparser.parser.CCJSqlParserManager;
import net.sf.jsqlparser.statement.Statement;
import net.sf.jsqlparser.statement.alter.Alter;
import net.sf.jsqlparser.statement.comment.Comment;
import net.sf.jsqlparser.statement.create.table.CreateTable;
import org.springframework.core.io.ClassPathResource;
import org.springframework.stereotype.Component;

import javax.annotation.PostConstruct;
import javax.sql.DataSource;
import java.io.IOException;
import java.io.StringReader;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.*;

/**
 * 初始化表结构
 *
 * @author Administrator
 */
@Slf4j
@Component
public class TableInitializer {

    private DataSource dataSource;

    private static DbTypeEnum DB_TYPE;

    private static Map<DbTypeEnum,  String> DB_TYPE_SQL_FILE;

    private List<String> TABLE_NAME_ARRAY = Arrays.asList("sys_audit_log");

    private Map<String,List<String>> TABLE_DDL = new HashMap<>();

    static {
        DB_TYPE_SQL_FILE = new HashMap<>();
        DB_TYPE_SQL_FILE.put(DbTypeEnum.MYSQL, "sql/mysql/data-change-tracker.sql");
    }

    /**
     * 构造函数
     * @param dataSource
     */
    @SneakyThrows
    public TableInitializer(DataSource dataSource) {
        this.dataSource = dataSource;
        try {
            DatabaseMetaData metaData = dataSource.getConnection().getMetaData();
            String databaseProductName = metaData.getDatabaseProductName();
            DB_TYPE = DbTypeEnum.getDbType(databaseProductName);
            readTableDdl();
        } catch (IOException e) {
            log.error("读取SQL文件失败: {}", e.getMessage(), e);
        }
    }

    @PostConstruct
    public void init() throws SQLException, IOException {
        for (String tableName : TABLE_NAME_ARRAY) {
            if(!tableExists(tableName)) {
                createTable(tableName);
            }
        }
    }

    /**
     * 判断表是否存在
     * @param tableName
     * @return
     * @throws SQLException
     */
    private boolean tableExists(String tableName) throws SQLException {
        try (Connection connection = dataSource.getConnection()) {
            DatabaseMetaData metaData = connection.getMetaData();
            ResultSet tables = metaData.getTables(null, null, convertTableName(tableName), new String[]{"TABLE"});
            return tables.next();
        }
    }

    /**
     * 转换表名
     * @param tableName
     * @return
     */
    private String convertTableName(String tableName) {
        switch (DB_TYPE) {
            case XU_GU:
                return tableName.toUpperCase(Locale.ROOT);
            default:
                return tableName;
        }
    }

    /**
     * 创建表
     * @param tableName
     * @throws SQLException
     * @throws IOException
     */
    private void createTable(String tableName) throws SQLException, IOException {
         if(!TABLE_DDL.containsKey(tableName)) {
             readTableDdl();
         }
        List<String> sqlList = TABLE_DDL.get(tableName);
         if(CollUtil.isNotEmpty(sqlList)) {
             try (Connection connection = dataSource.getConnection();
                  java.sql.Statement statement = connection.createStatement()) {
                 for (String sql : sqlList) {
                     statement.executeUpdate(sql);
                 }
             }
         } else {
             log.warn("未找到表{}的DDL语句", tableName);
         }
    }

    /**
     * 读取SQL文件
     * @return
     * @throws IOException
     */
    private void readTableDdl() throws IOException {
        String sqlContent = readSqlFile();
        CCJSqlParserManager parserManager = new CCJSqlParserManager();
        String[] statements = sqlContent.split(";");
        for (String stmt : statements) {
            if (stmt.trim().isEmpty()) {
                continue;
            }

            try {
                Statement statement = parserManager.parse(new StringReader(stmt + ";"));
                if (statement instanceof CreateTable) {
                    String name = ((CreateTable) statement).getTable().getName().toLowerCase(Locale.ROOT);
                    List<String> sqlList = TABLE_DDL.getOrDefault(name, new ArrayList<>());
                    if(!sqlList.contains(stmt)) {
                        sqlList.add(stmt);
                    }
                    if(!TABLE_DDL.containsKey(name)) {
                        TABLE_DDL.put(name, sqlList);
                    }
                } else if (statement instanceof Comment) {
                    String name = ((Comment) statement).getTable().getName().toLowerCase(Locale.ROOT);
                    List<String> sqlList = TABLE_DDL.getOrDefault(name, new ArrayList<>());
                    if(!sqlList.contains(stmt)) {
                        sqlList.add(stmt);
                    }
                    if(!TABLE_DDL.containsKey(name)) {
                        TABLE_DDL.put(name, sqlList);
                    }
                } else if (statement instanceof Alter) {
                    String name = ((Alter) statement).getTable().getName().toLowerCase(Locale.ROOT);
                    List<String> sqlList = TABLE_DDL.getOrDefault(name, new ArrayList<>());
                    if(!sqlList.contains(stmt)) {
                        sqlList.add(stmt);
                    }
                    if(!TABLE_DDL.containsKey(name)) {
                        TABLE_DDL.put(name, sqlList);
                    }
                }

            } catch (Exception e) {
                // 忽略非CREATE TABLE语句的解析错误
            }
        }
    }

    /**
     * 读取SQL文件
     * @return
     * @throws IOException
     */
    private String readSqlFile() throws IOException {
        String fileName = DB_TYPE_SQL_FILE.getOrDefault(DB_TYPE, "");
        if(StrUtil.isBlank(fileName)) {
            log.warn("未找到{}数据库的DDL文件", DB_TYPE);
            return "";
        }
        ClassPathResource resource = new ClassPathResource(fileName);
        byte [] content = new byte[resource.getInputStream().available()];
        resource.getInputStream().read(content);
        return new String(content);
    }
}
